import sqlite3
import sys
import traceback
from tkinter import messagebox

conn = sqlite3.connect('../database.db')
conn.row_factory = sqlite3.Row
cursor = conn.cursor()

secret_card_table_index = {}
data_cache = {}
process_file_count = 0
process_first = False


def commit_data_cache():
    global data_cache
    print('start commit, {} finish'.format(file))
    for table_name in data_cache.keys():
        sql = "insert into {}(device, gate_id, car_type, card_type, pass_timestamp, location, card_tail, secret_card) values(?, ?, ?, ?, ?, ?, ?, ?)".format(table_name)
        cursor.executemany(sql, data_cache[table_name])
    conn.commit()
    data_cache = {}


if __name__ == '__main__':
    print('select gate_pass_info_index')
    sql = 'select * from gate_pass_info_index'
    result = conn.execute(sql).fetchall()
    for row in result:
        data = tuple(row)
        secret_card = data[0]
        index = data[1]
        secret_card_table_index[secret_card] = index
    print('finish select gate_pass_info_index')

    log = open('log.txt', 'w', encoding='utf8')
    # for i in range(0, 8000000, 100000):
    for i in range(135900000, 1664200000, 100000):
        # for i in range(1, 2, 100000):
        file = 'D:/PycharmProjects/PyTorchTest/read_test/202205part2.csv_results/202205part2.csv-{}-{}.CSV'.format(i, i + 100001)
        # file = 'D:/PycharmProjects/PyTorchTest/read_test/202205part2.csv_results/202205part2.csv-1-100000.CSV'
        with open(file, 'r',
                  encoding='utf8') as f:
            line_count = 0
            line_number = 0
            try:
                for line in f:
                    line_number += 1
                    if not process_first:
                        process_first = True
                        continue

                    if line == '"\n':
                        continue

                    if len(line) < 10:
                        print("line len < 10, file = {}, number = {}, line={}".format(file, line_number, line))
                        continue

                    line = line.replace('"', '').replace('\n', '').replace("'", "")
                    line_split = line.split(',')
                    if len(line_split) < 8 or line_split[7] == '':
                        error = '{} line_split len < 8, number = {}, line = {} \n'.format(file, line_number, line)
                        print(error)
                        log.write(error)
                        log.flush()
                        continue

                    index = secret_card_table_index[line_split[7]]
                    table_name = 'gate_pass_info_{}'.format(index)
                    if table_name not in data_cache:
                        data_cache[table_name] = []
                    data_cache[table_name].append(line_split)
                    line_count += 1
            except Exception as e:
                messagebox.showinfo("提示", "发生错误")
                print("insert exception, file = {}, number = {}, line={}".format(file, line_number, line))
                traceback.print_exc()
                sys.exit(-1)

        process_file_count += 1
        print('{} finish'.format(file, line_count))
        if process_file_count % 40 == 0:
            commit_data_cache()
    log.close()
    commit_data_cache()
    conn.close()